package org.opu.vcforms.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *  Data receiver from database.
 * Additional methods is: <ul>
 *  <li>{@link #clear()} - to drop old request data</li>
 *  <li>{@link #closeConnect()} - to close connect</li>
 *  <li>{@link #commit()} - to commit data after update query</li>
 *  <li>{@link #connect(String)} - create connection to databas</li>
 *  <li>{@link #setAutoCommit(boolean)} - auto commit</li>
 *  <li>{@link #sendQueryUpdate(String)} - send query for update</li></ul>
 * @author yura
 */
public class DbManager implements DataReceiver {

    private UserData prop;
    private boolean connected = false;
    private Connection conn;
    private Statement statement;
    private ResultSet result_set;
    private ResultSetMetaData meta_data;
    private boolean query_sent = false;

    /**
     *  Create new instance with connection to database, according with
     * given user data
     * @param prop connection properties: user data, and database properties
     * @throws ClassNotFoundException if troubles while loading JDBC driver
     * @throws SQLException it database cannot be accessed
     */
    public DbManager(UserData prop)throws ClassNotFoundException, SQLException{
        this.prop = prop;
        String url = getDbUrlString();
        connect(url);
    }

    /**
     * The copy constructor
     * @param dbManager parent database manager
     * @see Cloneable
     */
    private DbManager(DbManager dbManager){
        this.conn = dbManager.conn;
        connected = dbManager.connected;
    }

    /**
     *  Create connection to database, according with given URL
     * @param url connection address with additional properties
     * @throws ClassNotFoundException if troubles while loading JDBC driver
     * @throws SQLException it database cannot be accessed
     */
    public final void connect(String url) throws ClassNotFoundException,
            SQLException {
        
        try {
            Class.forName(prop.getDbProperties().getClassName());
            conn = DriverManager.getConnection(url, prop.getUser(), prop.getPassword());
            connected = true;

            //TODO: Yuriy Dunko: It's must be done in another way !
            String dbn = System.getProperty("database.name");
            if (dbn != null && dbn.equals("postgresql")) {
                Statement query = conn.createStatement();
                query.execute("set client_encoding to 'UNICODE'");
            }
        } catch (ClassNotFoundException ex) {
            System.err.println(getClass().getName() + ":");
            System.err.println("Troubles while loading JDBC driver - "
                    + prop.getDbProperties() + ": " + ex.getLocalizedMessage());
            throw ex;
        } catch (SQLException ex) {
            System.err.println(getClass().getName() + ":");
            System.err.println("Troubles - database cannot be accessed:"
                    + ex.getLocalizedMessage());
            throw ex;
        }
    }

    public final String getDbUrlString(){
        return new StringBuilder()
                .append("jdbc:")
                .append(prop.getDbProperties().getName())
                .append("://")
                .append(prop.getDbHost())
                .append(":")
                .append(prop.getDbPort())
                .append("/")
                .append(prop.getDbName())
                .toString();
    }

    @Override
    public boolean sendQuery(String query) {
        try {
            if (query_sent) {
                //return false;
                clear();
            }
            statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            result_set = statement.executeQuery(query);
            meta_data = result_set.getMetaData();
            query_sent = true;
            return true;
        } catch (SQLException ex) {
            System.err.println(getClass().getName() + ":");
            System.err.println("\tError - " + ex);
            System.err.println("\tQUERY - " + query);
            return false;
        }
    }

    /**
     * Sends the query. Uses for nonSELECT-queries.
     * @param query String - SQL-statement to execute.
     * @return boolean - true if executed successfully.
     */
    public boolean sendQueryUpdate(String query) {
        try {
            if (query_sent) {
                //return false;
                clear();
            }
            statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            statement.executeUpdate(query);
            return true;
        } catch (SQLException ex) {
            System.err.println(getClass().getName() + ":");
            System.err.println("\tError - " + ex);
            System.err.println("\tQUERY - " + query);
            return false;
        }
    }

    @Override
    public int getColumns() {
        if (query_sent) {
            try {
                return meta_data.getColumnCount();
            } catch (SQLException ex) {
                return 0;
            }
        }
        return 0;
    }

    @Override
    public int getRows() {
        try {
            if (!query_sent) {
                return 0;
            }
            result_set.last();
            return result_set.getRow();
        } catch (Exception ex) {
            return 0;
        }
    }

    /**
     * Returns string value.
     * @param row int - number of row (the first is 1).
     * @param col int - number of column (the first is 1).
     * @return String
     */
    public String getString(int row, int col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getString(col);
            }
            return "";
        } catch (Exception ex) {
            return "";
        }
    }

    /**
     * Returns string value.
     * @param row int - number of row (the first is 1).
     * @param col String - column name.
     * @return String
     */
    public String getString(int row, String col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getString(col);
            }
            return "";
        } catch (Exception ex) {
            return "";
        }
    }

    @Override
    public Object getObject(int row, String col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getObject(col);
            }
            return "";
        } catch (Exception ex) {
            return "";
        }
    }

    @Override
    public Object getObject(int row, int col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getObject(col);
            }
            return "";
        } catch (Exception ex) {
            return "";
        }
    }

    /**
     * Returns int value.
     * @param row int - number of row (the first is 1).
     * @param col int - number of column (the first is 1).
     * @return String
     */
    public int getInt(int row, int col) {
        try {
            if (!query_sent) {
                return 0;
            }
            if (result_set.absolute(row)) {
                return result_set.getInt(col);
            }
            return 0;
        } catch (Exception ex) {
            return 0;
        }

    }

    /**
     * Returns string value.
     * @param row int - number of row (the first is 1).
     * @param col String - column name.
     * @return String
     */
    public int getInt(int row, String col) {
        try {
            if (!query_sent) {
                return 0;
            }
            if (result_set.absolute(row)) {
                return result_set.getInt(col);
            }
            return 0;
        } catch (Exception ex) {
            return 0;
        }

    }

    @Override
    public String[] getHeads(){
        try {
            if (!query_sent) {
                return new String[]{""};
            }
            
            
            ResultSetMetaData metaData = result_set.getMetaData();
            int count = metaData.getColumnCount();
            List<String> l = new ArrayList<String>(count);

            for (int i = 1; i <= count; i++) {
                l.add(metaData.getColumnName(i));
            }

            return l.toArray(new String[count]);
        } catch (Exception ex) {
            return new String[]{""};
        }
    }

    /**
     * Clears query statement.
     */
    public void clear() {
        try {
            if (query_sent) {
                if (statement != null) {
                    statement.close();
                }
                query_sent = false;
            }
        } catch (SQLException ex) {
            System.err.println("Error closing statement!\n");
            query_sent = false;
        }
    }

    public void setAutoCommit(boolean autoCommit) {
        try {
            conn.setAutoCommit(autoCommit);
        } catch (SQLException ex) {
        }
    }

    public void commit() {
        try {
            conn.commit();
        } catch (SQLException ex) {
        }
    }

    protected void closeConnect() {
        try {
            if (connected) {
                conn.close();
                connected = false;
            }
        } catch (Exception ex) {
        }
    }

    @Override
    protected void finalize() throws Throwable {
        super.finalize();
        closeConnect();
    }

    @Override
    protected DbManager clone() throws CloneNotSupportedException {
        return new DbManager(this);
    }
}
